package cn.bitkit.assist.database;

import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.StringJoiner;

import cn.bitkit.assist.database.vo.ColumnVO;
import cn.bitkit.assist.database.vo.TableVO;
import cn.bitkit.assist.jdbc.JdbcConnection;
import cn.bitkit.assist.jdbc.JdbcHelper;
import cn.bitkit.base.constants.DialectType;
import cn.bitkit.base.util.StringUtil;
import cn.bitkit.tools.file.TextHelper;


/**
 * 
 * @author changbo
 *
 */
public class TableHelper {

	public static List<ColumnVO> queryColumns(String table){
		return queryColumns(table, null);
	}

	public static List<ColumnVO> queryColumns(String table, String database){
		String template = "";
		if(JdbcConnection.getDialectType() == DialectType.oracle){
			template = "select col.column_name columnName, " +
					"col.data_type dataType, " +
					"col.data_length dataLength, " +
					"col.data_scale dataScale, " +
					"decode(col.column_name, 'ID', 'true', 'false') primary_key primaryKey, " +
					"col.nullable nullAble, " +
					"com.comments \"comment\" " +
					"from user_tab_columns col " +
					"left join user_col_comments com " +
					"on com.table_name = col.table_name and com.column_name = col.column_name " +
					"where col.table_name = '${table}' " +
					"order by col.column_id";
		}else if(JdbcConnection.getDialectType() == DialectType.mysql){
			template = "select column_name columnName, " +
					"data_type dataType, " +
					"character_maximum_length dataLength, " +
					"numeric_scale dataScale, " +
					"if(column_key = 'PRI', 'true', 'false') primaryKey, " +
					"if(is_nullable = 'YES', 'true', 'false') nullAble, " +
					"column_comment comment " +
					"from information_schema.COLUMNS where table_name = '${table}' " +
					"${databaseWhere} " +
					"ORDER BY ORDINAL_POSITION ";
		}
		String sql = TextHelper.loadString(template)
				.setValue("table", table)
				.setValue("databaseWhere", database == null ? "" : "AND table_schema = '"+database +"'")
				.getText();
		return JdbcHelper.query(sql, ColumnVO.class);
	}

	public static String toSelectColumns(List<ColumnVO> list, String table){
		StringJoiner sj = new StringJoiner(", ", "select ", "\nfrom "+table);
		for(ColumnVO column : list) {
			sj.add(column.getColumnName());
		}
		return sj.toString();
	}


	public static List<Map<String, Object>> queryIndex(String table){
		String sql = "";
		if(JdbcConnection.getDialectType() == DialectType.oracle){
			sql = "select * from user_indexes where table_name='"+table+"';";
		}else if(JdbcConnection.getDialectType() == DialectType.mysql){
			sql = "show index from " + table;
		}
		List<Map<String, Object>> list = JdbcHelper.query(sql);

		return list;
	}

	public static List<TableVO> queryTables(){
		return queryTables(null);
	}

	public static List<TableVO> queryTables(String database){
		String sql = "";
		if(JdbcConnection.getDialectType() == DialectType.oracle){
			sql = "select table_name tableName, comments \"comment\" from user_tab_comments ";
		}else if(JdbcConnection.getDialectType() == DialectType.mysql){
			sql = "select table_name tableName, table_comment comment from information_schema.tables ";
			if(StringUtil.hasValue(database)) {
				sql += " where table_schema = '"+database+"' ";
			}
		}
		return JdbcHelper.query(sql, TableVO.class);
	}

	public static TableVO getTable(String table) {
		return getTable(table, null);
	}

	public static TableVO getTable(String table, String database) {
		List<TableVO> list = queryTables(database);
		Optional<TableVO> optional = list.stream()
				.filter(e -> StringUtil.isEqualIgnoreCase(table, e.getTableName())).findFirst();
		if(!optional.isPresent()) {
			return null;
		}
		return optional.get();
	}

}
